package com.skeeey.aim.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class QuarterStatistic {
	private String year = "2013";
	private int quarter = -1;
	private Workbook workbook = null;
	private Sheet sheet0 = null;
	
	public static class Pair {
		private String key;
		private String val;
		
		public Pair(String key, String val) {
			this.key = key;
			this.val = val;
		}
		
		public String getKey() {
			return this.key;
		}
		
		public String getVal() {
			return this.val;
		}
	} 
	
	public void doStatistic(String year, int quarter) throws IOException {
		if(quarter >=1 || quarter <=4) {
			this.quarter = quarter;
			this.year = year;
			Map<Integer, Pair> rows = findValidFiles();
			for(Entry<Integer, Pair> e : rows.entrySet()) {
				Pair p = e.getValue();
				setRecords0(e.getKey().intValue(), p.getKey(), getRecords0(p.getVal()));
				setRecords1(e.getKey().intValue(), p.getKey(), getRecords1(p.getVal()));
			}
			createQSFile();
		} else {
			throw new IllegalArgumentException("请选择正确的季度！");
		}
	}
	
	private Map<Integer, Pair> findValidFiles() {
		Map<Integer, Pair> rows = new HashMap<Integer, Pair>();
		File qFile = new File(AIMUtil.getSystemConfig("execl.q.path"));
		InputStream in = null;
		try {
			in = new FileInputStream(qFile);
			workbook = new HSSFWorkbook(in);
			sheet0 = workbook.getSheet("分车型统计");
			String rootPath = AIMUtil.getSystemConfig("data.storage.path");
			for(int i = 3; i < sheet0.getLastRowNum() - 3; i++) {
				Row row = sheet0.getRow(i);
				if(AIMUtil.isNumeric(AIMUtil.getValueFrom(row.getCell(1)))) {
					String rssc = AIMUtil.getValueFrom(row.getCell(2));
					String province = AIMUtil.getValueFrom(row.getCell(4));
					String city = AIMUtil.getValueFrom(row.getCell(5)) + "市";
					String netCode = AIMUtil.getValueFrom(row.getCell(6));
					String dealerShortName = AIMUtil.getValueFrom(row.getCell(9));
					File path = new File(rootPath + File.separator 
								+ rssc + File.separator 
								+ province + File.separator 
								+ city + File.separator 
								+ dealerShortName);
					if(path.exists() && path.isDirectory()) {
						File[] files = path.listFiles();
						int count = 0;
						for(File subPath : files) {
							if(subPath.isDirectory()) {
								String date = subPath.getName();
								if(isSuitableDate(date, this.year, this.quarter)) {
									if(count > 0) {
										throw new IllegalArgumentException("[" + dealerShortName + "]有重复统计的数据" + "，请先处理");
									}
									rows.put(Integer.valueOf(i), new Pair(date, path + File.separator 
											+ date + File.separator +
											"上海大众库存信息整理表" + netCode + "-" + dealerShortName + "-" + date + ".xls"));
									count++;
								}
							}
						}
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					// do nothing
				}
			}
		}
		
		return rows;
	}
	
	public static boolean isSuitableDate(String date, String year, int quarter) {
		boolean r = false;
		String[] dateStr = date.split("-");
		if(year.equals(dateStr[0])) {
			int month = Integer.parseInt(dateStr[1]);
			
			switch (quarter) {
			case 1:
				if(month > 0 && month < 4) {
					r = true;
				}
				break;
			case 2:
				if(month > 3 && month < 7) {
					r = true;
				}
				break;
			case 3:
				if(month > 6 && month < 10) {
					r = true;
				}
				break;
			case 4:
				if(month > 9 && month < 13) {
					r = true;
				}
				break;
			}
		}
		return r;
	}
	
	private Map<Integer, List<Integer>> getRecords0(String filePath) {
		Map<Integer, List<Integer>> records = new HashMap<Integer, List<Integer>>();
		File file = new File(filePath);
		if(file.exists() && file.isFile()) {
			InputStream in = null;
			try {
				in = new FileInputStream(file);
				Workbook workbook = new HSSFWorkbook(in);
				Sheet sheet = workbook.getSheet("检查报告");
				for(int i = 4; i < 11; i++) {
					Row row = sheet.getRow(i);
					List<Integer> counts = new ArrayList<Integer>();
					for(int j = 2; j < 22; j++) {
						counts.add(Integer.valueOf((int)(row.getCell(j).getNumericCellValue())));
					}
					records.put(Integer.valueOf(i), counts);
				}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (in != null) {
					try {
						in.close();
					} catch (IOException e) {
						// do nothing
					}
				}
			}
		}
		return records;
	}
	
	private List<Integer> getRecords1(String filePath) {
		List<Integer> records = new ArrayList<Integer>();
		File file = new File(filePath);
		if(file.exists() && file.isFile()) {
			InputStream in = null;
			try {
				in = new FileInputStream(file);
				Workbook workbook = new HSSFWorkbook(in);
				Sheet sheet0 = workbook.getSheet("在库车辆-状态一");
				int counts0 = 0;
				for(int i = 5; i < sheet0.getLastRowNum(); i++) {
					String cellValue = AIMUtil.getValueFrom(sheet0.getRow(i).getCell(1));
					if(cellValue == null || cellValue.length() == 0 || !AIMUtil.isNumeric(cellValue)) {
						break;
					}
					counts0++;
				}
				records.add(Integer.valueOf(counts0));
				count0(workbook.getSheet("在库车辆-状态二"), records);
				count1(workbook.getSheet("系统外车辆"), records);
				count2(workbook.getSheet("虚拟未查到"), records);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (in != null) {
					try {
						in.close();
					} catch (IOException e) {
						// do nothing
					}
				}
			}
		}
		return records;
	}
	
	private void count0(Sheet sheet, List<Integer> records) throws UnsupportedEncodingException {
		int counts1 = 0; //已卖-未录入系统
		int counts2 = 0; //转卖-系统未出库
		int counts3 = 0; //转买-未实际到库
		int counts4 = 0; //调运至二级网点或在外展出
		int counts5 = 0; //其他
		for(int i = 6; i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String firstCellValue = AIMUtil.getValueFrom(row.getCell(1));
			if(firstCellValue == null || firstCellValue.length() == 0 || !AIMUtil.isNumeric(firstCellValue)) {
				break;
			}
			//index of reason cell is 11
			String cellValue = AIMUtil.getValueFrom(row.getCell(11));
			if(cellValue != null) {
				if(cellValue.equals("已卖-未录入系统")) {
					counts1++;
				} else if(cellValue.equals("转卖-系统未出库")) {
					counts2++;
				} else if(cellValue.equals("转买-未实际到库")) {
					counts3++;
				} else if(cellValue.equals("调运至二级网点或在外展出")) {
					counts4++;
				} else if(cellValue.equals("其他")) {
					counts5++;
				}
			}
		}
		records.add(Integer.valueOf(counts1));
		records.add(Integer.valueOf(counts2));
		records.add(Integer.valueOf(counts3));
		records.add(Integer.valueOf(counts4));
		records.add(Integer.valueOf(counts5));
	}
	
	private void count1(Sheet sheet, List<Integer> records) throws UnsupportedEncodingException {
		int counts6 = 0; //客户未提车
		int counts7 = 0; //转卖转入车辆
		int counts8 = 0; //客户退回的车
		int counts9 = 0; //库存盘查当天到车，还未入库
		int counts10 = 0; //其他原因
		for(int i = 6; i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String firstCellValue = AIMUtil.getValueFrom(row.getCell(1));
			if(firstCellValue == null || firstCellValue.length() == 0 || !AIMUtil.isNumeric(firstCellValue)) {
				break;
			}
			//index of reason cell is 10
			String cellValue = AIMUtil.getValueFrom(row.getCell(10));
			if(cellValue != null) {
				if(cellValue.equals("客户未提车")) {
					counts6++;
				} else if(cellValue.equals("转卖转入车辆")) {
					counts7++;
				} else if(cellValue.equals("客户退回的车")) {
					counts8++;
				} else if(cellValue.equals("库存盘查当天到车，还未入库")) {
					counts9++;
				} else if(cellValue.equals("其他原因")) {
					counts10++;
				}
			}
		}
		records.add(Integer.valueOf(counts6));
		records.add(Integer.valueOf(counts7));
		records.add(Integer.valueOf(counts8));
		records.add(Integer.valueOf(counts9));
		records.add(Integer.valueOf(counts10));
	}
	
	private void count2(Sheet sheet, List<Integer> records) throws UnsupportedEncodingException {
		int counts1 = 0; //已卖-未录入系统
		int counts2 = 0; //转卖-系统未出库
		int counts4 = 0; //调运至二级网点或在外展出
		int counts5 = 0; //其他
		for(int i = 4; i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String firstCellValue = AIMUtil.getValueFrom(row.getCell(1));
			if(firstCellValue == null || firstCellValue.length() == 0 || !AIMUtil.isNumeric(firstCellValue)) {
				break;
			}
			//index of reason cell is 8
			String cellValue = AIMUtil.getValueFrom(row.getCell(8));
			if(cellValue != null) {
				if(cellValue.equals("已卖-未录入系统")) {
					counts1++;
				} else if(cellValue.equals("转卖-系统未出库")) {
					counts2++;
				} else if(cellValue.equals("调运至二级网点或在外展出")) {
					counts4++;
				} else if(cellValue.equals("其他")) {
					counts5++;
				}
			}
		}
		records.add(Integer.valueOf(counts2));
		records.add(Integer.valueOf(counts1));
		records.add(Integer.valueOf(counts4));
		records.add(Integer.valueOf(counts5));
	}
	
	private void setRecords0(int rowIndex, String date, Map<Integer, List<Integer>> records) {
		if(!records.isEmpty()) {
			Row row = sheet0.getRow(rowIndex);
			row.getCell(10).setCellValue(date);
			int[] indexes = {4,8,5,6,7,10,9};
			//start from 13
			int cellIndex = 13;
			for(int index : indexes) {
				List<Integer> record = records.get(Integer.valueOf(index));
				for(int i = 0; i < record.size(); i++) {
					row.getCell(cellIndex).setCellValue(record.get(i));
					cellIndex++;
				}
				//skip 1 cell
				cellIndex = cellIndex + 1;
			}
		}
	}
	
	private void setRecords1(int rowIndex, String date, List<Integer> records) {
		if(!records.isEmpty()) {
			Sheet sheet = workbook.getSheet("检查结果汇总");
			Row row = sheet.getRow(rowIndex);
			row.getCell(10).setCellValue(date);
			int j = 0;
			row.getCell(14).setCellValue(records.get(j).intValue());
			j = j + 1;
			for(int i = 16; i < 21; j++, i++) {
				row.getCell(i).setCellValue(records.get(j).intValue());
			}
			for(int i = 25; i < 30; j++, i++) {
				row.getCell(i).setCellValue(records.get(j).intValue());
			}
			for(int i = 31; i < 35; j++, i++) {
				row.getCell(i).setCellValue(records.get(j).intValue());
			}
		}
	}
	
	private void createQSFile() throws IOException {
		FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
		evaluator.evaluateAll();
		File sourceFile = new File(AIMUtil.getSystemConfig("quarter.statistic.path") +
				File.separator + year + "-Q" + quarter + "-统计表.xls");
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(sourceFile);
			workbook.write(out);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					// do nothing
				}
			}
		}
		
		String destName = year + "-Q" + quarter;
		FileUtils.copyFile(sourceFile, new File(AIMUtil.getSystemConfig("execl.temp.path") + File.separator + destName + ".xls"));
	}
	
	public static void main(String[] args) throws IOException {
		QuarterStatistic quarterStatistic = new QuarterStatistic();
		quarterStatistic.doStatistic("2013", 1);
	}

}
